using System;
using System.Text;
using TccAg.VO;
using System.Data;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace TccAg.DA
{
    public class DisciplinaDA
    {
        private String queryInsert = "INSERT INTO disciplina( DADOS_DISCIPLINA, SEMESTRE, CARGAHORARIA, CUNHO, STATUS) VALUES( @dados_disciplina, @semestre, @cargahoraria, @cunho, @status)";
        private String queryUpdate = "UPDATE disciplina SET dados_disciplina = @dados_disciplina, semestre = @semestre, cargahoraria = @cargahoraria, cunho = @cunho, status = @status WHERE ID = @id";
        private String queryDelete = "DELETE FROM disciplina WHERE ID = @id";
        private String queryLoadAll = "SELECT  id, dados_disciplina, semestre, cargahoraria, cunho, status FROM disciplina";
        private String queryLoadByPk = "SELECT  id, dados_disciplina, semestre, cargahoraria, cunho, status FROM disciplina WHERE ID = @id";
        private String queryLoadByFk = "SELECT  id, dados_disciplina, semestre, cargahoraria, cunho, status FROM disciplina WHERE SEMESTRE = @semestre";
        private String queryLoadByProfessor = 
            @"SELECT  
                id, 
                dados_disciplina, 
                semestre, 
                cargahoraria, 
                cunho, 
                status 
              FROM 
                disciplina
              JOIN
                disc_professor on (disc_professor.disciplina = disciplina.id) 
              WHERE 
                disc_professor.professor = @id_professor";
        
        private SqlConnection conexao = new SqlConnection("Pegar conexão");

        //INSERIR 
        public int inserir(DisciplinaVO disciplina)
        {
            try
            {
                SqlCommand cmd;
                cmd = conexao.CreateCommand();
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = queryInsert;

                if (disciplina.Dadosdisciplina.Id != 0)
                        cmd.Parameters.Add(new SqlParameter("@dados_disciplina", (object) disciplina.Dadosdisciplina.Id));
                else
                        cmd.Parameters.Add(new SqlParameter("@dados_disciplina", (object) DBNull.Value));

                if (disciplina.Semestre.Id != 0)
                        cmd.Parameters.Add(new SqlParameter("@semestre", (object) disciplina.Semestre.Id));
                else
                        cmd.Parameters.Add(new SqlParameter("@semestre", (object) DBNull.Value));

                if (disciplina.Cargahoraria != 0)
                        cmd.Parameters.Add(new SqlParameter("@cargahoraria", (object) disciplina.Cargahoraria));
                else
                        cmd.Parameters.Add(new SqlParameter("@cargahoraria", (object) DBNull.Value));

                if (disciplina.Cunho != 0)
                        cmd.Parameters.Add(new SqlParameter("@cunho", (object) disciplina.Cunho));
                else
                        cmd.Parameters.Add(new SqlParameter("@cunho", (object) DBNull.Value));

                if (disciplina.Status != 0)
                        cmd.Parameters.Add(new SqlParameter("@status", (object) disciplina.Status));
                else
                        cmd.Parameters.Add(new SqlParameter("@status", (object) DBNull.Value));

                conexao.Open();
                return cmd.ExecuteNonQuery();

            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conexao.State == System.Data.ConnectionState.Open) conexao.Close();
            }
        }

        //ATUALIZAR 
        public int atualizar(DisciplinaVO disciplina)
        {
            try
            {
                SqlCommand cmd;
                cmd = conexao.CreateCommand();
                if (disciplina.Id != 0)
                    cmd.Parameters.Add(new SqlParameter("@id", (object)disciplina.Id));
                else
                    throw new Exception("Parâmetro necessário não enviado para o DA");

                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = queryUpdate;

                if (disciplina.Dadosdisciplina.Id != 0)
                    cmd.Parameters.Add(new SqlParameter("@dados_disciplina", (object)disciplina.Dadosdisciplina.Id));
                else
                    cmd.Parameters.Add(new SqlParameter("@dados_disciplina", (object)DBNull.Value));

                if (disciplina.Semestre.Id != 0)
                    cmd.Parameters.Add(new SqlParameter("@semestre", (object)disciplina.Semestre.Id));
                else
                    cmd.Parameters.Add(new SqlParameter("@semestre", (object)DBNull.Value));

                if (disciplina.Cargahoraria != 0)
                        cmd.Parameters.Add(new SqlParameter("@cargahoraria", (object) disciplina.Cargahoraria));
                else
                        cmd.Parameters.Add(new SqlParameter("@cargahoraria", (object) DBNull.Value));
                
                if (disciplina.Cunho != 0)
                        cmd.Parameters.Add(new SqlParameter("@cunho", (object) disciplina.Cunho));
                else
                        cmd.Parameters.Add(new SqlParameter("@cunho", (object) DBNull.Value));
                
                if (disciplina.Status != 0)
                        cmd.Parameters.Add(new SqlParameter("@status", (object) disciplina.Status));
                else
                        cmd.Parameters.Add(new SqlParameter("@status", (object) DBNull.Value));
                
                conexao.Open();
                
                return cmd.ExecuteNonQuery();

            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conexao.State == System.Data.ConnectionState.Open) conexao.Close();
            }
        }

        //EXCLUIR 
        public int excluir(DisciplinaVO disciplina)
        {
            try
            {
                SqlCommand cmd;
                cmd = conexao.CreateCommand();
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = queryDelete;

                if (disciplina.Id != 0)
                    cmd.Parameters.Add(new SqlParameter("@id", (object)disciplina.Id));
                else
                    throw new Exception("Parâmetro necessário não enviado para o DA");

                conexao.Open();
                return cmd.ExecuteNonQuery();

            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conexao.State == System.Data.ConnectionState.Open) conexao.Close();
            }
        }

        //LOAD ALL 
        public List<DisciplinaVO> loadAll()
        {
            try
            {
                List<DisciplinaVO> objRetorno = new List<DisciplinaVO>();
                DisciplinaVO objTemp;
                SqlCommand cmd;
                SqlDataReader dr;
                cmd = conexao.CreateCommand();
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = queryLoadAll;

                conexao.Open();
                dr = cmd.ExecuteReader();

                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        objTemp = new DisciplinaVO();
                        if (dr["id"] != DBNull.Value)
                           objTemp.Id = (int)dr["id"];

                        if (dr["dados_disciplina"] != DBNull.Value)
                            objTemp.Dadosdisciplina =
                                new DadosDisciplinaDA().loadByPk((int)dr["dados_disciplina"]);

                        if (dr["semestre"] != DBNull.Value)
                            objTemp.Semestre = new SemestreDA().loadByPk((int)dr["semestre"]);

                        if (dr["cargahoraria"] != DBNull.Value)
                           objTemp.Cargahoraria = (int)dr["cargahoraria"];

                        if (dr["cunho"] != DBNull.Value)
                           objTemp.Cunho = (Classes.Utils.Util.TipoDisciplina)dr["cunho"];

                        if (dr["status"] != DBNull.Value)
                           objTemp.Status = (Classes.Utils.Util.Status)dr["status"];

                        objRetorno.Add(objTemp);
                    }
                }
                return objRetorno;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conexao.State == System.Data.ConnectionState.Open) conexao.Close();
            }
        }

        //LOAD BY PK - Passa um objeto do tipo VO para em casos onde usar chave primária composta ter essas propriedades preenchidas
        public DisciplinaVO loadByPk(DisciplinaVO disciplina)
        {
            try
            {
                DisciplinaVO objRetorno = null;
                DisciplinaVO objTemp;
                SqlCommand cmd;
                SqlDataReader dr;
                cmd = conexao.CreateCommand();
                if (disciplina.Id != 0)
                    cmd.Parameters.Add(new SqlParameter("@id", (object)disciplina.Id));
                else
                    throw new Exception("Parâmetro necessário não enviado para o DA");

                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = queryLoadByPk;

                conexao.Open();
                dr = cmd.ExecuteReader();

                if (dr.HasRows)
                {
                    dr.Read();
                    {
                        objTemp = new DisciplinaVO();
                        if (dr["id"] != DBNull.Value)
                           objTemp.Id = (int)dr["id"];

                        if (dr["dados_disciplina"] != DBNull.Value)
                            objTemp.Dadosdisciplina =
                                new DadosDisciplinaDA().loadByPk((int)dr["dados_disciplina"]);

                        if (dr["semestre"] != DBNull.Value)
                            objTemp.Semestre = new SemestreDA().loadByPk((int)dr["semestre"]);

                        if (dr["cargahoraria"] != DBNull.Value)
                           objTemp.Cargahoraria = (int)dr["cargahoraria"];

                        if (dr["cunho"] != DBNull.Value)
                           objTemp.Cunho = (Classes.Utils.Util.TipoDisciplina)dr["cunho"];

                        if (dr["status"] != DBNull.Value)
                           objTemp.Status = (Classes.Utils.Util.Status)dr["status"];

                        objRetorno = objTemp;
                    }
                }
                return objRetorno;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conexao.State == System.Data.ConnectionState.Open) conexao.Close();
            }
        }

        //LOAD BY FK - Passa um objeto do tipo VO para em casos onde usar chave estrangeira composta ter essas propriedades preenchidas
        public List<DisciplinaVO> loadByFk(DisciplinaVO disciplina)
        {
            try
            {
                List<DisciplinaVO> objRetorno = new List<DisciplinaVO>();
                DisciplinaVO objTemp;
                SqlCommand cmd;
                SqlDataReader dr;
                cmd = conexao.CreateCommand();
                if (disciplina.Semestre.Id != 0)
                    cmd.Parameters.Add(new SqlParameter("@semestre", (object)disciplina.Semestre.Id));
                else
                    throw new Exception("Parâmetro necessário não enviado para o DA");

                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = queryLoadByFk;

                conexao.Open();
                dr = cmd.ExecuteReader();

                if (dr.HasRows)
                {
                    dr.Read();
                    {
                        objTemp = new DisciplinaVO();
                        if (dr["id"] != DBNull.Value)
                           objTemp.Id = (int)dr["id"];

                        if (dr["dados_disciplina"] != DBNull.Value)
                            objTemp.Dadosdisciplina =
                                new DadosDisciplinaDA().loadByPk((int)dr["dados_disciplina"]);

                        if (dr["semestre"] != DBNull.Value)
                            objTemp.Semestre = new SemestreDA().loadByPk((int)dr["semestre"]);

                        if (dr["cargahoraria"] != DBNull.Value)
                           objTemp.Cargahoraria = (int)dr["cargahoraria"];

                        if (dr["cunho"] != DBNull.Value)
                           objTemp.Cunho = (Classes.Utils.Util.TipoDisciplina)dr["cunho"];

                        if (dr["status"] != DBNull.Value)
                           objTemp.Status = (Classes.Utils.Util.Status)dr["status"];

                        objRetorno.Add(objTemp);
                    }
                }
                return objRetorno;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conexao.State == System.Data.ConnectionState.Open) conexao.Close();
            }
        }

        //DATATABLE LOAD ALL 
        public DataTable dataTableLoadAll()
        {
            try
            {
                SqlCommand cmd;
                cmd = conexao.CreateCommand();
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = queryLoadAll;

                conexao.Open();
                SqlDataAdapter dtAdpt = new SqlDataAdapter(cmd);
                DataTable dtTbl = new DataTable("tab_disciplina");

                dtAdpt.Fill(dtTbl);
                return dtTbl;

            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conexao.State == System.Data.ConnectionState.Open) conexao.Close();
            }
        }

        public List<DisciplinaVO> loadByProfessor(ProfessorVO professor)
        {
            try
            {
                List<DisciplinaVO> objRetorno = new List<DisciplinaVO>();
                DisciplinaVO objTemp;
                SqlCommand cmd;
                SqlDataReader dr;
                cmd = conexao.CreateCommand();
                if (professor.Id != 0)
                    cmd.Parameters.Add(new SqlParameter("@id_professor", (object)professor.Id));
                else
                    throw new Exception("Parâmetro necessário não enviado para o DA");

                int idDados = 0;

                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = queryLoadByFk;

                conexao.Open();
                dr = cmd.ExecuteReader();


                if (dr.HasRows)
                {
                    dr.Read();
                    {
                        objTemp = new DisciplinaVO();
                        if (dr["id"] != DBNull.Value)
                            objTemp.Id = (int)dr["id"];

                        if (dr["dados_disciplina"] != DBNull.Value)
                            objTemp.Dadosdisciplina = 
                                new DadosDisciplinaDA().loadByPk((int)dr["dados_disciplina"]);

                        if (dr["semestre"] != DBNull.Value)
                            objTemp.Semestre = new SemestreDA().loadByPk((int)dr["semestre"]);

                        if (dr["cargahoraria"] != DBNull.Value)
                            objTemp.Cargahoraria = (int)dr["cargahoraria"];

                        if (dr["cunho"] != DBNull.Value)
                            objTemp.Cunho = (Classes.Utils.Util.TipoDisciplina)dr["cunho"];

                        if (dr["status"] != DBNull.Value)
                            objTemp.Status = (Classes.Utils.Util.Status)dr["status"];

                        objRetorno.Add(objTemp);
                    }
                }
                return objRetorno;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conexao.State == System.Data.ConnectionState.Open) conexao.Close();
            }
        }

    }
}
